package com.yc.testupload.dao;

import com.yc.testupload.model.Account;
import com.yc.testupload.util.DatabaseUtil;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 账号数据访问对象
 * 处理用户账号关系表的数据库操作
 */
public class AccountDAO {
    private static final Logger logger = LogManager.getLogger(AccountDAO.class);

    /**
     * 根据用户ID获取账号列表
     * 
     * @param userId 用户ID
     * @return 账号列表
     */
    public List<Account> getAccountsByUserId(int userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Account> accounts = new ArrayList<>();

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "SELECT * FROM user_account_relation WHERE user_id = ? ORDER BY create_time DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Account account = new Account();
                account.setId(rs.getInt("id"));
                account.setUserId(rs.getInt("user_id"));
                // 修正account_type类型，数据库中是tinyint
                account.setAccountType(String.valueOf(rs.getInt("account_type")));
                account.setAccountId(rs.getString("account_id"));
                account.setAccountName(rs.getString("account_name"));
                // 设置默认值，因为表中没有这些字段
                account.setContactEmail("");
                account.setStatus("已授权");

                // 使用数据库中实际的create_time字段
                account.setCreatedAt(rs.getTimestamp("create_time"));

                // 使用数据库中实际的expire_time字段
                if (rs.getObject("expire_time") != null) {
                    account.setExpiresAt(rs.getTimestamp("expire_time"));
                }

                accounts.add(account);
            }
        } catch (SQLException e) {
            logger.error("根据用户ID获取账号列表时出错: {}", e.getMessage(), e);
        } finally {
            closeResources(conn, pstmt, rs);
        }

        return accounts;
    }

    /**
     * 添加账号
     * 
     * @param account 账号对象
     * @return 是否添加成功
     */
    public boolean addAccount(Account account) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "INSERT INTO user_account_relation (user_id, account_type, account_id, account_name, app_secret, access_token, refresh_token, expire_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, account.getUserId());
            // 修正account_type类型，从String转为int
            pstmt.setInt(2, Integer.parseInt(account.getAccountType()));
            pstmt.setString(3, account.getAccountId());
            pstmt.setString(4, account.getAccountName());
            pstmt.setString(5, account.getAppSecret() != null ? account.getAppSecret() : ""); // app_secret
            // 设置默认值，因为表中有这些字段但Account类中没有对应的属性
            pstmt.setString(6, ""); // access_token
            pstmt.setString(7, ""); // refresh_token

            // 设置过期时间
            if (account.getExpiresAt() != null) {
                pstmt.setTimestamp(8, new Timestamp(account.getExpiresAt().getTime()));
            } else {
                pstmt.setNull(8, Types.TIMESTAMP);
            }

            logger.debug("执行SQL插入，appSecret: {}", account.getAppSecret() != null ? "已设置" : "未设置");

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            logger.error("添加账号时出错: {}", e.getMessage(), e);
        } finally {
            closeResources(conn, pstmt, null);
        }

        return false;
    }

    /**
     * 更新账号
     * 
     * @param account 账号对象
     * @return 是否更新成功
     */
    public boolean updateAccount(Account account) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "UPDATE user_account_relation SET account_name = ?, contact_email = ?, status = ? WHERE id = ? AND user_id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, account.getAccountName());
            pstmt.setString(2, account.getContactEmail());
            pstmt.setString(3, account.getStatus());
            pstmt.setInt(4, account.getId());
            pstmt.setInt(5, account.getUserId());

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            logger.error("更新账号时出错: {}", e.getMessage(), e);
        } finally {
            closeResources(conn, pstmt, null);
        }

        return false;
    }

    /**
     * 删除账号
     * 
     * @param id     账号ID
     * @param userId 用户ID
     * @return 是否删除成功
     */
    public boolean deleteAccount(int id, int userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "DELETE FROM user_account_relation WHERE id = ? AND user_id = ?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, id);
            pstmt.setInt(2, userId);

            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            logger.error("删除账号时出错: {}", e.getMessage(), e);
        } finally {
            closeResources(conn, pstmt, null);
        }

        return false;
    }

    /**
     * 关闭数据库资源
     */
    private void closeResources(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                logger.error("关闭ResultSet时出错: {}", e.getMessage(), e);
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                logger.error("关闭PreparedStatement时出错: {}", e.getMessage(), e);
            }
        }
        if (conn != null) {
            DatabaseUtil.closeConnection(conn);
        }
    }
}